# date: 2024/1/16 11:26
# author: xqx
# description：数据操作
import datetime
import os
import sqlite3
import time


def get_db_conn():
    BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    db_path = os.path.join(BASE_DIR, "milestone.db")
    conn = sqlite3.connect(db_path)
    return conn


def user_login(user, pwd):
    """
    根据用户名和密码创建用户
    """
    # 1. 先查询数据库中用户是否存在，存在则返回失败
    conn = get_db_conn()
    ar = conn.cursor().execute('select user_name,user_pwd from users where user_name = ?', (user,)).fetchone()
    if ar is not None:
        # 2.判断用户名密码是否正确
        if ar[0] == user and ar[1] == pwd:
            return 1, '用户信息正确，登录成功'
        return 2, '密码不正确'
    else:
        return 3, '用户名不存在'


def user_register(user, pwd):
    """
    根据用户名和密码创建用户
    """
    # 直接创建用户
    conn = get_db_conn()
    conn.cursor().execute('insert into users (user_name,user_pwd) values (?,?)', (user, pwd))
    conn.commit()
    conn.close()
    return 0, '用户注册成功'


def user_pwd_change(user, pwd):
    """
    根据用户名和密码创建用户
    """
    # 直接创建用户
    conn = get_db_conn()
    conn.cursor().execute('update  users set user_pwd=? where user_name=?', (pwd, user))
    conn.commit()
    conn.close()
    return 0, '修改密码成功'


def delete_user(user):
    """
    根据用户名删除用户。登录界面可注销，用户必在表中存在
    """
    # 1. 先查询数据库中用户是否存在，不存在则返回失败
    conn = get_db_conn()
    user_info = conn.cursor().execute('select * from users where user_name = ?', (user,)).fetchone()
    if user_info is None:
        return 1, '用户不存在'
    # 2.删除用户
    conn.cursor().execute('delete from users where user_name = ?', (user,))
    conn.commit()
    conn.close()
    return 0, '用户删除成功'


def get_user_id(user_name):
    """
    根据用户名字获取用户id，默认用户名已存在
    """
    conn = get_db_conn()
    user_info = conn.cursor().execute('select user_id from users where user_name = ?', (user_name,)).fetchone()
    return user_info[0]


def get_project_id(project_name, user_id):
    """
    根据项目名字获取项目id，默认用户名已存在
    """
    conn = get_db_conn()
    user_info = conn.cursor().execute('select project_id from projects where project_name = ? and user_id=?',
                                      (project_name, user_id,)).fetchone()
    return user_info[0]


def create_project(cur_project_name, cur_user_id):
    """
    该用户下创建项目
    """
    # 1. 先查询数据库中项目名字是否存在，存在则返回失败
    conn = get_db_conn()
    user_project_info = conn.cursor().execute('select project_name from projects where user_id = ?',
                                              (cur_user_id,)).fetchall()
    print(user_project_info)
    if len(user_project_info) != 0:
        for i in user_project_info:
            if cur_project_name in i:
                return 1, '该用户下项目名字已存在'
    # 2.开始创建项目
    conn.execute('insert into projects (project_name,user_id) values (?,?)', (cur_project_name, cur_user_id))
    conn.commit()
    conn.close()
    return 0, '项目创建成功'


def delete_project(project_ids, cur_user_id):
    """
    删除该用户下部分项目
    """
    conn = get_db_conn()
    for i in project_ids:
        conn.cursor().execute('delete from projects where user_id = ? and project_id=?', (cur_user_id, i))
    conn.commit()
    conn.close()
    return 0, '项目删除成功'


def delete_user_project(cur_user_id):
    """
    删除该用户下部分项目
    """
    conn = get_db_conn()
    conn.cursor().execute('delete from projects where user_id = ? ', (cur_user_id,))
    conn.commit()
    conn.close()
    return 0, '项目删除成功'


def get_user_project(cur_user_id):
    """
    根绝用户id查询项目名称
    """
    conn = get_db_conn()
    project_id_list = conn.cursor().execute(
        'select project_name from projects where user_id = ? order by created_date desc',
        (cur_user_id,)).fetchall()
    ans = []
    for i in project_id_list:
        ans.append(i[0])
    return ans


def add_project_progress(cur_project_id, progress_content):
    conn = get_db_conn()
    # 1.查询今天是否有进度，没有则新增，有则修改
    today_progress = conn.cursor().execute(
        'select progress_id from progress where project_id=? and created_date=date(datetime(\'now\',\'localtime\'));',
        (cur_project_id,)).fetchone()
    # print(today_progress)
    if today_progress is None:
        conn.cursor().execute('insert into progress (project_id,progress_content) values (?,?)',
                              (cur_project_id, progress_content))
        conn.commit()
        conn.close()
        return 0, '新增进度成功'
    # 2.存在则修改进度
    conn.cursor().execute(
        'update progress set progress_content=? where project_id=? and progress_id=?',
        (progress_content, cur_project_id, today_progress[0]))

    conn.commit()
    conn.close()
    return 0, '更新进度成功'


def delete_project_progress(cur_project_id):
    """
    删除项目下的所有进度
    """
    conn = get_db_conn()
    conn.cursor().execute('delete from progress where project_id = ? ', (cur_project_id,))
    conn.commit()
    conn.close()
    return 0, '项目进度删除成功'


def get_project_progress(cur_project_id):
    """
    根绝项目id查询项目的进度
    """
    conn = get_db_conn()
    progress_list = conn.cursor().execute(
        'select strftime(\'%Y-%m-%d\',created_date),progress_content from progress where project_id = ? order by created_date desc',
        (cur_project_id,)).fetchall()
    ans = {}
    for i in progress_list:
        ans[i[0]] = i[1]
    return ans


if __name__ == '__main__':
    print(create_project("消消乐", 1))
    print(create_project("每日一题", 1))
    # print(user_register('ljx', 'xqx123456l.'))
